package pe.edu.upc.dsd.ws;

import java.sql.*;


public class ObtenerDatosBD {

	String sql = "";
	Connection conn = getConnection();

	public CinesList ObtenerCinesBD() throws SQLException {
		Statement stm = conn.createStatement();
		sql = "select Cine_id, Cine_Nombre  from Cine";
		ResultSet rst = stm.executeQuery(sql);
		System.out.println("Sentencia sql:" + sql );
		CinesList list = new CinesList();

		while (rst.next()) {
			list.add(new Cine(Integer.toString(rst.getInt(1)), rst.getString(2)));

		}

		rst.close();
		stm.close();
		conn.close();

		return list;

	}	
	
	
	public PeliculasList ObtenerPeliculasBD() throws SQLException {
		Statement stm = conn.createStatement();
		sql = "SELECT Pelic_Id,Pelic_Nombre,Pelic_Sinopsis,Pelic_Genero,Pelic_Pais,Pelic_Director,Pelic_Censura,Pelic_Web,Pelic_Precio,Pelic_Duracion ,Pelic_Trailer FROM Pelicula";
		ResultSet rst = stm.executeQuery(sql);

		PeliculasList list = new PeliculasList();
		System.out.println("Sentencia sql:" + sql );
		while (rst.next()) {
			list.add(new Pelicula(Integer.toString(rst.getInt(1)), rst.getString(2) ,rst.getString(3),rst.getString(4),rst.getString(5),rst.getString(6),rst.getString(7),rst.getString(8),Double.toString( rst.getDouble(9)) , Integer.toString(rst.getInt(10)), rst.getString(11) ));
		}

		rst.close();
		stm.close();
		conn.close();

		return list;

	}
	
	
	public CartelerasList ObtenerCartelerasBD( String pelicula, String cine) throws SQLException {
		Statement stm = conn.createStatement();
		
		sql = " select cine.Cine_Id, cine.Cine_Nombre,[Local].Local_Direc ,Pelicula.Pelic_Id ,  Pelicula.Pelic_Nombre ,  CONVERT(varchar , Cartelera.Carte_Dia,103 ), CONVERT(varchar , Cartelera.Carte_Hora_Inicio ,103 ) ,Pelicula.Pelic_Sinopsis," ; 
		sql = sql +	 " Pelicula.Pelic_Pais,	   Pelicula.Pelic_Duracion,	   pelicula.Pelic_Genero,	   pelicula.Pelic_Censura from Cartelera , Sala ,  [Local] , Cine , Pelicula " ; 
		sql = sql +  " where cartelera.Sala_Id = Sala.Sala_Id  and Sala.Local_Id = [Local].Local_Id  and [Local].Cine_Id = Cine.Cine_Id and Pelicula.Pelic_Id  = Cartelera.Pelic_Id " ;
		if (pelicula.equals("0")==false) {
			sql = sql + " and Pelicula.Pelic_Id = " + pelicula;	
		}
		
		if (cine.equals("0")==false) {
			sql = sql + " and cine.Cine_Id = " + cine	;
		}
		
		
		ResultSet rst = stm.executeQuery(sql);

		CartelerasList list = new CartelerasList(); 

		System.out.println("Sentencia sql:" + sql );
		
		while (rst.next()) {
			list.add( new Cartelera( Integer.toString(rst.getInt(1)) , rst.getString(2),  rst.getString(3), Integer.toString(rst.getInt(4)), rst.getString(5),  rst.getString(6), rst.getString(7), rst.getString(8), rst.getString(9),Integer.toString(rst.getInt(10)),rst.getString(11), rst.getString(12)) );
		}

		rst.close();
		stm.close();
		conn.close();

		return list;

	}
	

	private static Connection getConnection() {
		ConexionBD cn = new ConexionBD();
		return cn.getConnection();
	}

}
